-- Deletes the hierarchy branched rooted at the hierarchyKey sent in.  This includes all descendants
CREATE PROCEDURE [dbo].[asi_HierarchyDeleteBranch] @hierarchyKey uniqueidentifier AS
DECLARE
    @LowSort int, @HighSort int, @RootHierarchyKey uniqueidentifier
BEGIN
    -- first, find the high (if it is there) as the lowest sort order that is higher than the one sent in
    -- and with a depth equal or shallower
    SELECT @RootHierarchyKey = a.RootHierarchyKey, @LowSort = a.SortOrder, @HighSort = Min(b.SortOrder)
      FROM Hierarchy a LEFT OUTER JOIN Hierarchy b
        ON a.RootHierarchyKey = b.RootHierarchyKey
       AND a.SortOrder < b.SortOrder
       AND a.Depth >= b.Depth
     WHERE a.HierarchyKey = @hierarchyKey
     GROUP BY a.RootHierarchyKey, a.SortOrder
    
    -- if we found the high sort, delete everything in between
    IF @HighSort IS NOT NULL
    BEGIN
        DELETE
          FROM Hierarchy
         WHERE RootHierarchyKey = @RootHierarchyKey
           AND SortOrder >= @LowSort
           AND SortOrder < @HighSort
    END
    -- if we didn't, delete everything beyond this one
    ELSE
    BEGIN
        DELETE
          FROM Hierarchy
         WHERE RootHierarchyKey = @RootHierarchyKey
           AND SortOrder >= @LowSort
    END
    
END

GO